package com.je.common.base.table.service.impl;

import com.google.common.base.Joiner;
import com.google.common.base.Strings;
import com.je.common.base.DynaBean;
import com.je.common.base.constants.ConstantVars;
import com.je.common.base.service.rpc.BeanService;
import com.je.common.base.table.service.BuildingSqlService;
import com.je.common.base.util.DBSqlUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Map;

@Service("buildingSqlServerSqlService")
public class BuildingSqlServerSqlServiceImpl extends AbstractBuildingSqlService implements BuildingSqlService {

    @Autowired
    private BeanService beanService;

    /**
     * 研发部:云凤程
     * 生成数据库的查询操作(全部是通过预处理的)
     * @param dynaBean
     * @return
     */
    @Override
    public String getInsertSql(DynaBean dynaBean){
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        DynaBean table = beanService.getResourceTable(tableCode);
        //2.拼装预处理的SQL
        StringBuilder insertSql = new StringBuilder();
        String fieldNames = beanService.getNames4Sql(table);//表字典集合用","分开
        String values = beanService.getValues4Sql(table);//表字典集合用 格式化成 :name,:age
        if(StringUtils.isNotEmpty(fieldNames)){
            fieldNames= DBSqlUtils.formatOracleToSqlServer(fieldNames);
        }
        insertSql.append("INSERT INTO ["+tableCode+"]("+fieldNames+") VALUES ("+values+")");
        return insertSql.toString();
    }

    /**
     * 得到Bean修改的sql语句 (主要用于表格列表更新)
     * @param tableCode
     * @param pkName
     * @param changes
     * @return
     */
    @Override
    public String getUpdateSql(String tableCode, String pkName, Map changes){
        StringBuffer updateSql=new StringBuffer();
        updateSql.append("UPDATE ["+tableCode+"] SET ");
        String pkValue="";
        for(Object obj:changes.entrySet()){
            Map.Entry entry=(Map.Entry) obj;
            String k;
            if(entry.getKey() == null || Strings.isNullOrEmpty(entry.getKey().toString())){
                k = "";
            }else{
                k = entry.getKey().toString();
            }

            if(pkName.equals(k)){
                if(entry.getValue() == null || Strings.isNullOrEmpty(entry.getValue().toString())){
                    pkValue = "";
                }else{
                    pkValue = entry.getValue().toString();
                }
                continue;
            }

            String v=null;
            if(entry.getValue()!=null){
                if(entry.getValue() == null || Strings.isNullOrEmpty(entry.getValue().toString())){
                    v = "";
                }else{
                    v = entry.getValue().toString();
                }
                if(v.indexOf("'")>=0){
                    v=v.replaceAll("'", "''");
                }
            }
            if(v==null){
                updateSql.append("["+k+"]=NULL,");
            }else{
                updateSql.append("["+k+"]='"+v+"',");
            }
        }
        updateSql.deleteCharAt(updateSql.length()-1);
        updateSql.append(" WHERE ["+pkName+"]='"+pkValue+"'");
        return updateSql.toString();
    }

    /**
     * 研发部:云凤程
     * 生成数据根据where删除的SQL
     * @param ids 用,分开的主键id串
     * @param tableName 表名称
     * @param idName 表主键名字
     * @return
     */
    @Override
    public String getDeleteByIdsSql(String ids , String tableName , String idName){
        Integer paramCount=0;
        for(String id:ids.split(",")){
            if(!Strings.isNullOrEmpty(id)){
                paramCount++;
            }
        }
        String[] idArray=new String[paramCount];
        for(int i=0;i<paramCount;i++){
            idArray[i]="?";
        }
        String idValues= Joiner.on(",").join(idArray);
        if(paramCount<=0){
            idValues="''";
        }
        String deleteSql = "DELETE FROM "+tableName + " WHERE ["+idName+"] in ("+idValues+")";
        return deleteSql;
    }

    /**
     * 研发部:云凤程
     * 生成根据id查询一条数据的SQL
     * @param dynaBean
     * @return
     */
    @Override
    public String getSelecOnetByIdSql(DynaBean dynaBean){
        //1.得到表结构的描述信息
        String tableCode = dynaBean.getStr(BeanService.KEY_TABLE_CODE);
        //2.得到表的主键编码
        String tablePKCode = dynaBean.getStr(BeanService.KEY_PK_CODE);
        //3.得到表主键的值
        String tablePKValue = dynaBean.getStr(tablePKCode);
        //4.得到要查询的列
        String tableFileds = dynaBean.getStr(BeanService.KEY_QUERY_FIELDS,"");
        if(Strings.isNullOrEmpty(tableFileds)){
            tableFileds=dynaBean.getStr(BeanService.DEF_ALL_FIELDS);
        }
        String seletcSql= ConstantVars.BLANK_STR;
        tableFileds=DBSqlUtils.formatOracleToSqlServer(tableFileds);
        seletcSql = "SELECT "+tableFileds+" FROM "+tableCode+" WHERE ["+tablePKCode+"] = ?";
        //5.拼接SQl
        return seletcSql;
    }

}
